from ..config.db import LinkSql


class ReserveSql(LinkSql):

    def __init__(self, some_model=None):
        self.SomeModel = some_model
        LinkSql.__init__(self)

    def data_read(self, value):
        self.sesson.execute(value)
        return self.sesson.fetchall()

    def data_witer(self, value):
        self.sesson.execute(value)
        self.db.commit()

    def witer_sql(self, **kwargs):
        return f"""INSERT INTO {self.SomeModel} ({','.join(kwargs.keys())}) VALUES {tuple(kwargs.values())} ;"""

    def find_data(self, *args, **kwargs):
        return f"""SELECT * FROM {self.SomeModel} {self.some_filter(**kwargs) if kwargs else ''}"""

    def update_data(self, *args, **kwargs):
        return f"""UPDATE {self.SomeModel} SET {self.some_equal(**kwargs)} """

    def some_equal(self, **data):
        list_data = []
        if data:
            for data_key, data_value in data.items():
                data_value = 'null' if data_value == 'null' else data_value
                data_value = f"""'{data_value}'""" if isinstance(data_value, str) else data_value
                data_value = f"""{data_value}""" if isinstance(data_value, int) else data_value
                list_data.append(f"""{data_key} = {data_value}""")
        return ','.join(list_data)

    def some_filter(self, **data):
        list_filter = []
        if data:
            for data_key, data_value in data.items():
                if '__gl' in data_key:
                    data_key = data_key.replace('__gl', '')
                    list_filter.append(f"""{data_key} < '{data_value}'""")
                elif '__gt' in data_key:
                    data_key = data_key.replace('__gt', '')
                    list_filter.append(f"""{data_key} > '{data_value}'""")
                elif '__gle' in data_key:
                    data_key = data_key.replace('__gle', '')
                    list_filter.append(f"""{data_key} <= '{data_value}'""")
                elif '__gte' in data_key:
                    data_key = data_key.replace('__gte', '')
                    list_filter.append(f"""{data_key} >= '{data_value}'""")
                elif '__like' in data_key:
                    data_key = data_key.replace('__like', '')
                    list_filter.append(f"""{data_key} Like '%{data_value}%' """)
                elif '__not_in' in data_key:
                    data_key = data_key.replace('__not_in', '')
                    list_filter.append(f"""{data_key} NOT IN {tuple(data_value)} """)
                elif '__not' in data_key:
                    data_key = data_key.replace('__not', '')
                    list_filter.append(f"""{data_key} != '{data_value}'""")
                elif '__null' in data_key:
                    data_key = data_key.replace('__null', '')
                    list_filter.append(f"""{data_key} IS {'' if data_value else 'NOT '}NULL""")
                elif '__in' in data_key:
                    data_key = data_key.replace('__in', '')
                    list_filter.append(f"""{data_key} IN {tuple(data_value)} """)
                else:
                    list_filter.append(f"""{data_key} = '{data_value}'""")
        return 'WHERE ' + ' AND '.join(list_filter) if list_filter else ''

    def some_page(self):
        return self.pagenum, self.size, (int(self.pagenum) - 1) * self.size, int(self.pagenum) * self.size

    def count(self, **kwargs):
        kwargs = self.some_filter(**kwargs) if kwargs else ''
        return self.data_read(f"""SELECT  count(*) FROM {self.SomeModel} {kwargs}""")[0]['count(*)']

    def creat(self, **kwargs):
        self.data_witer(self.witer_sql(**kwargs))
        return self.sesson.lastrowid

    def where(self, *args, **kwargs):
        if self.data:
            where_sql = f"""{self.data};"""
            if kwargs:
                where_sql = where_sql.replace(';', f"""{self.some_filter(**kwargs)};""")
            self.data_witer(where_sql)
            return True
        else:
            return False

    def value(self, *args, **kwargs):
        value_sql = f"""{self.data.replace('*', ','.join(args)) if args else self.data};"""
        if self.typename == 'first':
            value_data = self.data_read(value_sql.replace(';', 'limit 0,1;'))
            return value_data[0] if value_data else None
        elif self.typename == 'page':
            page, size, star_page, end_page = self.some_page()
            count = self.count(**kwargs)
            page_count = count // size
            data = list(self.data_read(value_sql.replace(";", f"""LIMIT {star_page}, {end_page};""")))
            size, page_count = size if page_count else len(data), page_count if not count % size else page_count + 1
            return data, page, size, page_count, count
        else:
            return list(self.data_read(value_sql))

    def list(self, *args, **kwargs):
        self.data, self.typename = self.find_data(**kwargs), 'list'
        return self

    def first(self, *args, **kwargs):
        self.data, self.typename = self.find_data(**kwargs), 'first'
        return self

    def page(self, size=10, pagenum=None, *args, **kwargs):
        self.pagenum = 1 if not pagenum or pagenum == '' else pagenum
        self.size, self.data, self.typename = size, self.find_data(**kwargs), 'page'
        return self

    def update(self, *args, **kwargs):
        """
            修改sql
            :param args: None
            :param kwargs: 修改数据
            :return: 对象本身
        """

        self.data = self.update_data(**kwargs) if kwargs else None
        return self
